/*============================================================================
  File:     DIFForFULLBackup.sql

  Summary:  This script creates a system-wide SP SQLskills_DIFForFILL that
			works out what percentage of a database has changed since the
			previous full database backup.

  Date:     April 2008

  SQL Server Versions:
		10.0.1300.13 (SS2008 February CTP - CTP-6)
		9.00.3054.00 (SS2005 SP2)
------------------------------------------------------------------------------
  Copyright (C) 2008 Paul S. Randal, SQLskills.com
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you give due credit.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- create the function in MSDB
USE msdb;
GO

IF OBJECT_ID(N'SQLskillsConvertToExtents', N'FN') IS NOT NULL
	DROP FUNCTION SQLskillsConvertToExtents;
GO

/*
This function cracks the output from a DBCC PAGE dump
of an allocation bitmap. It takes a string in the form
"(1:8) - (1:16)" or "(1:8) -" and returns the number
of extents represented by the string.
Both the examples above equal 1 extent.
*/

CREATE FUNCTION SQLskillsConvertToExtents (
	@extents VARCHAR (100))
RETURNS INTEGER
AS
BEGIN
	DECLARE @extentTotal	INT = 0;
	DECLARE @colon			INT = CHARINDEX (':', @extents);
	
	DECLARE @firstExtent	INT;
	DECLARE @secondExtent	INT;

	-- check for the single extent case
	IF (CHARINDEX (':', @extents, @colon + 1) = 0)
		SET @extentTotal = 1;
	ELSE
		-- we're in the multi-extent case
		BEGIN
			SET @firstExtent = CONVERT (INT,
				SUBSTRING (@extents, @colon + 1, CHARINDEX (')', @extents, @colon) - @colon - 1));
			SET @colon = CHARINDEX (':', @extents, @colon + 1);
			SET @secondExtent = CONVERT (INT,
				SUBSTRING (@extents, @colon + 1, CHARINDEX (')', @extents, @colon) - @colon - 1));
			SET @extentTotal = (@secondExtent - @firstExtent) / 8 + 1;
		END

	RETURN @extentTotal;
END;
GO

USE master;
GO

IF OBJECT_ID (N'sp_SQLskills_DIFForFULLBackup', N'P') IS NOT NULL
   DROP PROCEDURE sp_SQLskills_DIFForFULLBackup;
GO

/*
This SP cracks all differential bitmap pages for all online
data files in a database. It creates a sum of changed extents
and reports it as follows (example small msdb):

EXECUTE sp_SQLskills_DIFForFULL 'msdb';
GO

Total Extents Changed Extents Percentage Changed
------------- --------------- ----------------------
102           56              54.9

Note that after a full backup you will always see some extents
marked as changed. The number will be 4 + (number of data files - 1).
These extents contain the file headers of each file plus the
roots of some of the critical system tables in file 1.
The number for msdb may be around 20.
*/
CREATE PROCEDURE sp_SQLskills_DIFForFULLBackup (
   @database_name VARCHAR (128))
AS
BEGIN
	SET NOCOUNT ON;

	-- create the temp table
	IF EXISTS (SELECT 1 FROM msdb.sys.objects WHERE NAME = N'SQLskillsDBCCPage')
		DROP TABLE msdb.dbo.SQLskillsDBCCPage;

	CREATE TABLE msdb.dbo.SQLskillsDBCCPage (
		[ParentObject]	VARCHAR (100),
		[Object]		VARCHAR (100),
		[Field]			VARCHAR (100),
		[Value]			VARCHAR (100));	

	DECLARE @fileID			INT;
	DECLARE @fileSizePages	INT;
	DECLARE @extentID		INT;
	DECLARE @pageID			INT;
	DECLARE	@diffTotal		INT = 0;
	DECLARE @sizeTotal		INT = 0;
	DECLARE @total			INT;
	DECLARE @dbccPageString	VARCHAR (200);

	-- setup a cursor for all online data files in the database
	DECLARE files CURSOR FOR
		SELECT file_id, size FROM master.sys.master_files
		WHERE type_desc = 'ROWS'
		AND state_desc = 'ONLINE'
		AND database_id = DB_ID (@database_name);

	OPEN files;

	FETCH NEXT FROM files INTO @fileID, @fileSizePages;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @extentID = 0;

		-- The size returned from master.sys.master_files is in
		-- pages - we need to convert to extents
		SELECT @sizeTotal = @sizeTotal + @fileSizePages / 8;

		WHILE (@extentID < @fileSizePages)
		BEGIN

			/*
			There may be an issue with the DIFF map page position
			on the four extents where PFS pages and GAM pages live
			(at page IDs 516855552, 1033711104, 1550566656, 2067422208)
			but I think we'll be ok.
			PFS pages are every 8088 pages (page 1, 8088, 16176, etc)
			GAM extents are every 511232 pages
			*/
			SELECT @pageID = @extentID + 6;

			-- build the dynamic SQL
			SELECT @dbccPageString = 'DBCC PAGE ('
				+ @database_name + ', '
				+ CAST (@fileID AS VARCHAR) + ', '
				+ CAST (@pageID AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS';

			-- empty out the temp table and insert into it again
			DELETE FROM msdb.dbo.SQLskillsDBCCPage;
			INSERT INTO msdb.dbo.SQLskillsDBCCPage EXECUTE (@dbccPageString);

			-- aggregate all the changed extents using the function
			SELECT @total = SUM (msdb.dbo.SQLskillsConvertToExtents ([Field]))
			FROM msdb.dbo.SQLskillsDBCCPage
				WHERE [Value] = '    CHANGED'
				AND [ParentObject] LIKE 'DIFF_MAP%';

			SET @diffTotal += @total;

			-- move to the next GAM extent
			SET @extentID += 511232;
		END

		FETCH NEXT FROM files INTO @fileID, @fileSizePages;
	END;

	-- clean up
	CLOSE files;
	DEALLOCATE files;

	DROP TABLE msdb.dbo.SQLskillsDBCCPage;

	-- output the results
	SELECT
		@sizeTotal AS total_extents,
		@diffTotal AS changed_extents,
		ROUND (
			(CONVERT (FLOAT, @diffTotal) /
			CONVERT (FLOAT, @sizeTotal)) * 100, 2) AS changed_percent;
END;
GO

-- mark the SP as a system object
EXECUTE sys.sp_MS_marksystemobject sp_SQLskills_DIFForFULLBackup;
GO

-- test to make sure everything was setup correctly
EXECUTE sp_SQLskills_DIFForFULLBackup 'msdb';
GO